package entity

import (
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"time"

	"gorm.io/gorm"
	"shqsoft.cn/pas3ui/base"
)

// ShJhcwJxfflbjxdr
type ShJhcwJxfflbjxdr struct {
	ID   int       `gorm:"AUTO_INCREMENT;primary_key;column:ID"`
	FFJD time.Time `gorm:"column:FFJD"`
	DPID int       `gorm:"column:DPID"`
	DPNM string    `gorm:"column:DPNM"`
	EMID int       `gorm:"column:EMID"`
	EMNO string    `gorm:"column:EMNO"`
	EMNM string    `gorm:"column:EMNM"`
	ZW   string    `gorm:"column:ZW"`
	ZJZD string    `gorm:"column:ZJZD"`
	LBJE float64   `gorm:"column:LBJE"`
	DRID int       `gorm:"column:DRID"`
	PDID int       `gorm:"column:PDID"`
}

// TableName
func (ShJhcwJxfflbjxdr) TableName() string {
	return "SH_JHCW_JXFF_LBJX"
}

// BeforeCreate
func (u *ShJhcwJxfflbjxdr) BeforeCreate(tx *gorm.DB) (err error) {
	jdint := datenfjj(u.FFJD)
	err = can_import(tx, jdint)
	if err != nil {
		return
	}
	var emid *int
	var dpid, pdid int
	var dpnm string
	var emno, emnm string
	var name string
	tx.Raw(`select "DPID","DPNM","EMID","EMNO","EMNM","PDID" from "V_USER_DEPT" where "EMNO"=?`, u.EMNO).Row().Scan(&dpid, &dpnm, &emid, &emno, &emnm, &pdid)
	if dpid == 0 {
		tx.Raw(`SELECT "DPID","PDID" FROM "V_DEPT" WHERE "DPNM" = ?`, u.DPNM).Row().Scan(&dpid, &pdid)
		if dpid == 0 {
			err = fmt.Errorf("机构名称%s在系统中不存在,请修改！", u.DPNM)
			return
		}
	} else {
		u.DPNM = dpnm
	}
	if emid == nil {
		tx.Raw(`select "ID","SysUserName" from "T_SysUser" where "SysUserID" = ?`, u.EMNO).Row().Scan(&emid, &name)
		if emid == nil {
			user := User{
				No:             u.EMNO,
				Name:           u.EMNM,
				OrganizationID: &dpid,
				PositionID:     7,
				SerialID:       8,
				STATUS:         "1",
			}
			err = tx.Create(&user).Error
			if err != nil {
				return
			}
			u.EMID = user.ID
		} else {
			u.EMNM = name
			u.EMID = *emid
		}
		return
	}
	u.EMID = *emid
	u.PDID = pdid
	u.DPID = dpid
	return
}
func can_import(tx *gorm.DB, dateInt int) error {
	date_str := strconv.Itoa(dateInt)
	year := date_str[0:4]
	month := date_str[4:]
	var sjid int
	if month == "01" {
		sjid = 1
	} else if month == "02" {
		sjid = 2
	} else if month == "03" {
		sjid = 3
	} else if month == "1" {
		sjid = 4
	} else if month == "04" {
		sjid = 5
	} else if month == "05" {
		sjid = 6
	} else if month == "06" {
		sjid = 7
	} else if month == "2" {
		sjid = 8
	} else if month == "07" {
		sjid = 19
	} else if month == "08" {
		sjid = 10
	} else if month == "09" {
		sjid = 11
	} else if month == "3" {
		sjid = 12
	} else if month == "10" {
		sjid = 13
	} else if month == "11" {
		sjid = 14
	} else if month == "12" {
		sjid = 15
	} else if month == "4" {
		sjid = 16
	}
	var state int
	tx.Raw(`select "CZ" from "SH_JHCW_JXSD_PERM" where "NIAN" = ? AND "SJ"=?`, year, sjid).Row().Scan(&state)
	if state == 1 {
		return nil
	}
	lb := "月份"
	if len(month) == 1 {
		lb = "季度"
	}
	err := fmt.Errorf("%s年%s%s没有开放导入或调整！", year, month, lb)
	return err
}

// LbjxAfterImport 导入后执行
func LbjxAfterImport(tx *gorm.DB, params map[string]string) (err error) {
	ffjd := params["FFJD"]
	var date time.Time
	date, err = time.Parse("2006-01-02", ffjd)
	if err != nil {
		return
	}
	quarter := datenfjj(date)
	var sqlstr string
	sqlstr = `INSERT INTO "SH_JHCW_JXFF_YGJX" ("FFJD","EMID" ,"EMNO" ,"EMNM" ,"DPID" ,"DPNM" ,"PDID" )
			  SELECT ? "FFJD","EMID" ,"EMNO" ,"EMNM" ,"DPID" ,"DPNM" ,"PDID"
			  from "v_USER_DEPT" where "EMNO"  NOT IN (SELECT "EMNO" from "SH_JHCW_JXFF_YGJX" where "FFJD" = ?)`
	err = tx.Exec(sqlstr, quarter, quarter).Error
	if err != nil {
		return
	}
	err = ImpJxffAfter(tx, quarter)
	if err != nil {
		return
	}
	sqlstr = `UPDATE "SH_JHCW_JXFF_YGJX" SET "LBJX" = 0 WHERE "FFJD" = ?`
	err = tx.Exec(sqlstr, quarter).Error
	if err != nil {
		return
	}
	if base.AppConf.Database.Mysql != "" {
		sqlstr = `UPDATE "SH_JHCW_JXFF_YGJX" A,(select "EMNO","EMID",sum("LBJE") "LBJE",min("ZW") "ZW" ,min("ZJZD") ZJZD from "SH_JHCW_JXFF_LBJX" where "FFJD"=? group by "EMNO","EMID") B
		set A."EMNO" = B."EMNO",A."EMID" = B."EMID","LBJX" = B."LBJE","EMZW"=B."ZW" ,"ZJDC" = B.ZJZD
		WHERE A."FFJD" = ? AND A."EMNO" = B."EMNO"`
	} else {
		sqlstr = `UPDATE "SH_JHCW_JXFF_YGJX" A
		set "EMNO" = B."EMNO","EMID" = B."EMID","LBJX" = B."LBJE"
		FROM (select "EMNO","EMID",sum("LBJE") "LBJE" from "SH_JHCW_JXFF_LBJX" where "FFJD"=? group by "EMNO","EMID") B
		WHERE A."FFJD" =?  AND A."EMNO" = B."EMNO"`
	}
	err = tx.Exec(sqlstr, ffjd, quarter).Error
	if err != nil {
		return
	}

	sqlstr = `select "EMNO"
	from "SH_JHCW_JXFF_LBJX" where "FFJD" = ? and "EMID" not in (select "EMID" from "SH_JHCW_JXFF_YGJX" where "FFJD" = ?)
	group by "EMNO"
	HAVING COUNT(*) >1`
	var rows *sql.Rows
	rows, err = tx.Raw(sqlstr, ffjd, quarter).Rows()
	if err != nil {
		return
	}
	defer rows.Close()
	emnos := make([]string, 0, 10)
	for rows.Next() {
		var emno string
		rows.Scan(&emno)
		emnos = append(emnos, emno)
	}
	if len(emnos) > 0 {
		err = fmt.Errorf("员工号%s重复，请修改", strings.Join(emnos, ","))
		return
	}
	sqlstr = `insert into "SH_JHCW_JXFF_YGJX"("EMNM","EMNO","DPNM","EMZW","ZJDC","LBJX","DPID","EMID","FFJD","PDID" )
	select "EMNM" ,"EMNO","DPNM","ZW" as "EMZW","ZJZD" as "ZJDC","LBJE" as "LBJX","DPID" ,"EMID",? "FFJD","PDID"
	from "SH_JHCW_JXFF_LBJX" where "FFJD" = ? and "EMID" not in (select "EMID" from "SH_JHCW_JXFF_YGJX" where "FFJD" = ?)`
	err = tx.Exec(sqlstr, quarter, ffjd, quarter).Error
	if err != nil {
		return
	}
	err = ImpJxffAfter(tx, quarter)
	if err != nil {
		return
	}
	return
}
func ImpJxffAfter(tx *gorm.DB, dtid int) (err error) {
	var sqlstr string
	if base.AppConf.Database.Mysql != "" {
		sqlstr = `update "SH_JHCW_JXFF_YGJX" set "YFHJ"=IFNULL("YJJX",0)+IFNULL("YWJX",0)+IFNULL("YWJX_ZXJL",0)+IFNULL("YWJX_ZXJL_KH",0)+
		IFNULL("YWJX_SGBJ1",0)+IFNULL("YWJX_SGBJ2",0)+IFNULL("YWJX_SGBJ3",0)+IFNULL("YWJX_SGBJ4",0)+IFNULL("YWJX_SGBJ5",0)+IFNULL("YWJX_SGBJ6",0)+
		IFNULL("YWJX_SGBJ7",0)+IFNULL("YWJX_SGBJ8",0)+IFNULL("YWJX_SGBJ9",0)+IFNULL("YWJX_SGBJ10",0)+IFNULL("YWJX_SGBJ11",0)+IFNULL("YWJX_SGBJ12",0) where "FFJD" = ?`
	} else {
		sqlstr = `update "SH_JHCW_JXFF_YGJX" set "YFHJ"=COALESCE("YJJX",0)+COALESCE("YWJX",0)+COALESCE("YWJX_ZXJL",0)+COALESCE("YWJX_ZXJL_KH",0)+
		COALESCE("YWJX_SGBJ1",0)+COALESCE("YWJX_SGBJ2",0)+COALESCE("YWJX_SGBJ3",0)+COALESCE("YWJX_SGBJ4",0)+COALESCE("YWJX_SGBJ5",0)+COALESCE("YWJX_SGBJ6",0)+
		COALESCE("YWJX_SGBJ7",0)+COALESCE("YWJX_SGBJ8",0)+COALESCE("YWJX_SGBJ9",0)+COALESCE("YWJX_SGBJ10",0)+COALESCE("YWJX_SGBJ11",0)+COALESCE("YWJX_SGBJ12",0) where "FFJD" = ?`
	}
	err = tx.Exec(sqlstr, dtid).Error
	if err != nil {
		return
	}
	if base.AppConf.Database.Mysql != "" {
		sqlstr = `update "SH_JHCW_JXFF_YGJX" set "SFJE"=IFNULL("YFHJ",0)+IFNULL("TZJE",0) where "FFJD" = ?`
	} else {
		sqlstr = `update "SH_JHCW_JXFF_YGJX" set "SFJE"=COALESCE("YFHJ",0)+COALESCE("TZJE",0) where "FFJD" = ?`
	}
	err = tx.Exec(sqlstr, dtid).Error
	if err != nil {
		return
	}
	sqlstr = fmt.Sprintf("drop table if exists TMP_JHCW_JXFF_YGJX_%d", dtid)
	err = base.CHDB.Exec(sqlstr).Error
	return
}

func ShJhcwJxfflbjxdrEF() base.EntityFunc {
	return func() interface{} {
		entity := ShJhcwJxfflbjxdr{}
		return &entity
	}
}
func ShJhcwJxfflbjxdrESF() base.EntitiesFunc {
	return func() interface{} {
		var entities []ShJhcwJxfflbjxdr
		return &entities
	}
}

func init() {
	base.DaoPoolObj.Put("ShJhcwJxfflbjxdr", base.NewDefaultDao(ShJhcwJxfflbjxdrEF(), ShJhcwJxfflbjxdrESF(), nil, "my"))
	base.SetAfterCallBack("ShJhcwJxfflbjxdr", LbjxAfterImport)
}
